import 'dart:convert';
import 'dart:math';
import 'dart:typed_data';
import 'package:flutter/foundation.dart';
import 'package:sqflite_common/sqflite.dart';
import 'package:sqflite_common/utils/utils.dart';
import 'package:sqflite_example_common/utils.dart';
import 'test_page.dart';

const bool kIsOhos = bool.fromEnvironment('dart.vm.product') ? false : false;

class _Data {
  late Database db;
}

class TypeTestPage extends TestPage {
  TypeTestPage({Key? key}) : super('Type tests', key: key) {
    test('int', () async {
      final path = await initDeleteDb('type_int.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute(
              'CREATE TABLE Test (id INTEGER PRIMARY KEY AUTOINCREMENT, value UNLIMITED INT)');
        },
      );

      try {
        final baseId = await insertValue(123456);
        expect(await getValue(baseId), 123456, reason: '基础整数存储失败');

        final negativeId = await insertValue(-123456);
        expect(await getValue(negativeId), -123456, reason: '负整数存储失败');

        final int32Min = -pow(2, 31).toInt(); // -2,147,483,648
        final int32Max = pow(2, 31).toInt() - 1; // 2,147,483,647

        final int32MinId = await insertValue(int32Min);
        expect(await getValue(int32MinId), int32Min, reason: '32位最小整数存储失败');

        final int32MaxId = await insertValue(int32Max);
        expect(await getValue(int32MaxId), int32Max, reason: '32位最大整数存储失败');

        if (!kIsWeb) {
          final bigInt = pow(2, 33).toInt(); // 8,589,934,592
          final bigIntId = await insertValue(bigInt);
          expect(await getValue(bigIntId), bigInt, reason: '超32位整数存储失败');
        }

        if (kIsWeb) {
          final webSafeMax = (pow(2, 53) - 1).toInt();
          final webSafeId = await insertValue(webSafeMax);
          expect(await getValue(webSafeId), webSafeMax,
              reason: 'Web安全整数上限存储失败');
        }

        if (!kIsOhos) {
          final int64Max =
              (pow(2, 63) - 1).toInt(); // 9,223,372,036,854,775,807
          final int64Min = -(pow(2, 63)).toInt(); // -9,223,372,036,854,775,808

          final int64MaxId = await insertValue(int64Max);
          expect(await getValue(int64MaxId), int64Max, reason: '64位最大整数存储失败');

          final int64MinId = await insertValue(int64Min);
          expect(await getValue(int64MinId), int64Min, reason: '64位最小整数存储失败');
        }

        const bigValue = 1234567890123456; // 1.2e+15（在64位范围内）
        final bigValueId = await insertValue(bigValue);
        expect(await getValue(bigValueId), bigValue, reason: '大整数精度丢失');
      } finally {
        await data.db.close();
      }
    });

    test('real', () async {
      final path = await initDeleteDb('type_real.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
            CREATE TABLE Test (
              id INTEGER PRIMARY KEY, 
              value REAL
            )
          ''');
        },
      );

      try {
        final doubleId = await insertValue(-1.1);
        expect(await getValue(doubleId), -1.1);

        final fractionId = await insertValue(1 / 3);
        expect(await getValue(fractionId), closeTo(1 / 3, 1e-9));

        if (!kIsOhos) {
          final bigDouble = pow(2, 63).toDouble() + 0.1;
          final bigDoubleId = await insertValue(bigDouble);
          expect(await getValue(bigDoubleId), bigDouble);

          final intToDouble = pow(2, 62).toDouble();
          final intToDoubleId = await insertValue(intToDouble);
          expect(await getValue(intToDoubleId), intToDouble);
        }

        const bigDoubleValue = 1234567890123456789.0;
        final bigDoubleValueId = await insertValue(bigDoubleValue);
        expect(await getValue(bigDoubleValueId), bigDoubleValue);
      } finally {
        await data.db.close();
      }
    });

    test('text', () async {
      final path = await initDeleteDb('type_text.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
            CREATE TABLE Test (
              id INTEGER PRIMARY KEY AUTOINCREMENT, 
              value TEXT
            )
          ''');
        },
      );

      try {
        final textId = await insertValue('simple text');
        expect(await getValue(textId), 'simple text');

        final nullId = await insertValue(null);
        expect(await getValue(nullId), null);

        final utf8Id = await insertValue('àöé');
        expect(await getValue(utf8Id), 'àöé');
      } finally {
        await data.db.close();
      }
    });

    test('blob', () async {
      final path = await initDeleteDb('type_blob.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
        CREATE TABLE Test (
          id INTEGER PRIMARY KEY, 
          value BLOB
        )
      ''');
        },
      );

      try {
        final textBytes = utf8.encode('simple text');
        final textBlobId = await insertValue(Uint8List.fromList(textBytes));
        final textBlobResult = await getValue(textBlobId);
        expect(textBlobResult, isA<Uint8List>(),
            reason: '文本转 Blob 读取类型错误，需为 Uint8List');
        expect(utf8.decode(textBlobResult as Uint8List), 'simple text');

        final byteData = ByteData(1)..setInt8(0, 1);
        final basicBlob = byteData.buffer.asUint8List();
        final basicBlobId = await insertValue(basicBlob);
        final basicBlobResult = await getValue(basicBlobId) as Uint8List;
        expect(basicBlobResult, basicBlob, reason: '基础 Blob 内容不一致');
        expect(basicBlobResult.length, 1, reason: '基础 Blob 长度异常');

        List<int> complexBlobSrc = [1, 2, 3, 4];
        Uint8List complexBlob = Uint8List.fromList(complexBlobSrc);
        final complexBlobId = await insertValue(complexBlob);
        final complexBlobResult = await getValue(complexBlobId) as Uint8List;
        expect(complexBlobResult.toList(), complexBlobSrc,
            reason:
                '复杂 Blob 内容不匹配，原始：$complexBlobSrc，实际：${complexBlobResult.toList()}');

        final emptyBlob = Uint8List(0);
        final emptyBlobId = await insertValue(emptyBlob);
        final emptyBlobResult = await getValue(emptyBlobId) as Uint8List;
        expect(emptyBlobResult, isEmpty, reason: '空 Blob 非空');

        if (!kIsOhos) {
          final hexResult = await data.db.rawQuery(
            'SELECT hex(value) FROM Test WHERE id = ?',
            [complexBlobId],
          );
          expect(
              hexResult.isNotEmpty && hexResult.first.values.isNotEmpty, true,
              reason: 'hex 查询结果为空');
          expect(hexResult.first.values.first, '01020304',
              reason: 'hex 结果错误，实际：${hexResult.first.values.first}');

          final rows = await data.db.query(
            'Test',
            where: 'id = ?',
            whereArgs: [complexBlobId],
          );
          expect(rows.length, 1, reason: 'Blob 查询结果数量异常');
          final rowBlob = rows.first['value'] as Uint8List;
          expect(listEquals(rowBlob.toList(), complexBlobSrc), true,
              reason: 'Blob 查询内容不一致');
        }
      } finally {
        await data.db.close();
      }
    });

    test('null', () async {
      final path = await initDeleteDb('type_null.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
            CREATE TABLE Test (
              id INTEGER PRIMARY KEY, 
              value TEXT
            )
          ''');
        },
      );

      try {
        final nullId = await insertValue(null);
        expect(await getValue(nullId), null);

        expect(await updateValue(nullId, 'dummy'), 1);
        expect(await getValue(nullId), 'dummy');

        expect(await updateValue(nullId, null), 1);
        expect(await getValue(nullId), null);
      } finally {
        await data.db.close();
      }
    });

    test('date_time', () async {
      final path = await initDeleteDb('type_date_time.db');
      data.db = await _openDatabase(
        path,
        version: 1,
        onCreate: (db, version) async {
          await db.execute('''
            CREATE TABLE Test (
              id INTEGER PRIMARY KEY, 
              value TEXT
            )
          ''');
        },
      );

      try {
        final dt = DateTime.fromMillisecondsSinceEpoch(1234567890);
        final id = await insertValue(dt.toIso8601String());
        final result = await getValue(id) as String;

        expect(result, dt.toIso8601String());

        final parsedDt = DateTime.parse(result);
        expect(parsedDt.millisecondsSinceEpoch, dt.millisecondsSinceEpoch);
      } finally {
        await data.db.close();
      }
    });

    test('bool', () async {
      try {
        data.db = await _openDatabase(
          inMemoryDatabasePath,
          version: 1,
          onCreate: (db, version) async {
            await db.execute('''
          CREATE TABLE Test (
            id INTEGER PRIMARY KEY, 
            value INTEGER
          )
        ''');
          },
        );
        final trueId = await insertValue(true);
        final falseId = await insertValue(false);
        debugPrint('插入 true ID: $trueId, false ID: $falseId');

        final trueResult = await getValue(trueId);
        final falseResult = await getValue(falseId);
        debugPrint('读取 true 结果: $trueResult, 类型: ${trueResult.runtimeType}');
        debugPrint('读取 false 结果: $falseResult, 类型: ${falseResult.runtimeType}');

        expect(trueResult, isA<int>(), reason: 'true 存 bool 后应返回 int');
        expect(falseResult, isA<int>(), reason: 'false 存 bool 后应返回 int');

        expect(trueResult, 1, reason: 'true 应转为 1');
        expect(falseResult, 0, reason: 'false 应转为 0');

        expect(trueResult == 1 ? true : false, isTrue, reason: '1 应转回 true');
        expect(falseResult == 0 ? false : true, isFalse, reason: '0 应转回 false');
      } catch (e, stackTrace) {
        debugPrint('bool 测试异常: $e\n$stackTrace');
        fail('bool 测试失败：$e');
      } finally {
        await data.db.close();
      }
    });
  }

  final _Data data = _Data();

  Future<Database> _openDatabase(
    String path, {
    required int version,
    required OnDatabaseCreateFn onCreate,
  }) async {
    if (kIsOhos) {
      debugPrint('鸿蒙平台：使用专属数据库初始化逻辑');
    }
    return await openDatabase(
      path,
      version: version,
      onCreate: onCreate,
    );
  }

  Future<dynamic> getValue(int id) async {
    final result = await data.db.query(
      'Test',
      where: 'id = ?',
      whereArgs: [id],
    );
    return result.isNotEmpty ? result.first['value'] : null;
  }

  Future<int> insertValue(dynamic value) async {
    if (value is bool) {
      value = value ? 1 : 0;
    }

    if (kIsOhos) {
      if (value is DateTime) {
        return await data.db.insert(
          'Test',
          {'value': value.toIso8601String()},
        );
      }
    }

    return await data.db.insert(
      'Test',
      {'value': value},
    );
  }

  Future<int> updateValue(int id, dynamic value) async {
    if (kIsOhos && value is DateTime) {
      return await data.db.update(
        'Test',
        {'value': value.toIso8601String()},
        where: 'id = ?',
        whereArgs: [id],
      );
    }

    return await data.db.update(
      'Test',
      {'value': value},
      where: 'id = ?',
      whereArgs: [id],
    );
  }
}
